package uk.co.droidinactu.sony.prst1.home.data;

import java.io.File;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import uk.co.droidinactu.sony.prst1.home.EditPreferences;
import uk.co.droidinactu.sony.prst1.home.R;
import uk.co.droidinactu.sony.prst1.home.db.SonyDatabaseController;
import uk.co.droidinactu.sony.prst1.home.view.BookSortBy;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.net.Uri;

public final class SonyDataModel {

    public static Bitmap getBookCoverImg(final Context cntxt, final String thumbnailFilename) {
        Bitmap bitmap = BitmapFactory.decodeFile("/mnt/sdcard/" + thumbnailFilename);
        if (bitmap == null) {
            bitmap = BitmapFactory.decodeFile("/mnt/extsd/" + thumbnailFilename);
        }
        if (bitmap == null) {
            bitmap = BitmapFactory.decodeResource(cntxt.getResources(), R.drawable.ic_missing_thumbnail_picture);
        }
        return bitmap;
    }

    private Context cntxt = null;

    private final SonyDatabaseController dbCntlr;

    public SonyDataModel(final Context ctx) {
        this.cntxt = ctx;
        this.dbCntlr = new SonyDatabaseController(this.cntxt);
        this.openDb();
    }

    public void closeDb() {
        this.dbCntlr.close();
    }

    public Book getBook(final Context cntxt, final long id) {
        final String sqlStr = "select * from " + Book.DATABASE_TABLE_NAME + " where _id=" + id
                + " union all select * from extDb." + Book.DATABASE_TABLE_NAME + " where _id=" + id;
        final Cursor cInt = this.dbCntlr.execSql(sqlStr, null);
        cInt.moveToFirst();
        final Book bk = new Book(cInt);
        cInt.close();
        return bk;
    }

    public Cursor getBooks(final Context cntxt, final BookSortBy sortBy) {
        final String sqlStr = "select * from " + Book.DATABASE_TABLE_NAME + " UNION ALL SELECT * FROM extDb."
                + Book.DATABASE_TABLE_NAME + " order by " + this.getSortOrderField(sortBy) + " asc";
        final Cursor cInt = this.dbCntlr.execSql(sqlStr, null);
        return cInt;
    }

    public Cursor getBooks(final Context cntxt, final String filterStr, final BookSortBy sortBy) {
        String sqlStr = "select * from " + Book.DATABASE_TABLE_NAME + " where title like '" + filterStr
                + "%' UNION ALL SELECT * FROM extDb." + Book.DATABASE_TABLE_NAME + " where title like '" + filterStr
                + "%' order by " + this.getSortOrderField(sortBy) + " asc";

        if (filterStr.equals("0")) {
            final StringBuilder likeStr = new StringBuilder(" title like");
            for (int x = 0; x <= 9; x++) {
                likeStr.append(" '" + x + "%' or title like ");
            }
            likeStr.replace(likeStr.lastIndexOf("or"), likeStr.length(), "");
            sqlStr = "select * from " + Book.DATABASE_TABLE_NAME + " where " + likeStr
                    + " UNION ALL SELECT * FROM extDb." + Book.DATABASE_TABLE_NAME + " where " + likeStr + " order by "
                    + this.getSortOrderField(sortBy) + " asc";
        }

        final Cursor cInt = this.dbCntlr.execSql(sqlStr, null);
        return cInt;
    }

    public Cursor getBooksInCollection(final Collection clctn, final BookSortBy sortBy) {
        return this.getBooksInCollection(clctn.title, sortBy);
    }

    public Cursor getBooksInCollection(final Context cntxt, final String collectionName, final int start,
            final int end, final BookSortBy sortBy) {
        final String sqlStr = "select books.* from books,collection,collections where books._id=collections.content_id and "
                + "collections.collection_id=collection._id and collection.title=? order by "
                + this.getSortOrderField(sortBy) + " asc limit " + start + "," + end;
        final Cursor cInt = this.dbCntlr.execSql(sqlStr, new String[] { collectionName });
        return cInt;
    }

    public Cursor getBooksInCollection(final String collectionName, final BookSortBy sortBy) {
        final String sqlStr = "select books.* from books,collection,collections where books._id=collections.content_id and "
                + "collections.collection_id=collection._id and collection.title=? order by "
                + this.getSortOrderField(sortBy) + " asc";
        return this.dbCntlr.execSql(sqlStr, new String[] { collectionName });
    }

    public Collection getCollection(final long id) {
        final String sqlStr = "select * from " + Collection.DATABASE_TABLE_NAME + " where _id='" + id
                + "' UNION ALL SELECT * FROM extDb." + Collection.DATABASE_TABLE_NAME + " final where _id='" + id
                + "' order by title asc";
        final Cursor cInt = this.dbCntlr.execSql(sqlStr, null);
        cInt.moveToFirst();
        final Collection b = new Collection(cInt);
        cInt.close();
        return b;
    }

    public Cursor getCollection(final String collectionName) {
        final String sqlStr = "select distinct title from " + Collection.DATABASE_TABLE_NAME + " where title='"
                + collectionName + "' UNION ALL SELECT * FROM extDb." + Collection.DATABASE_TABLE_NAME
                + " final where title='" + collectionName + "' order by title asc";
        final Cursor cInt = this.dbCntlr.execSql(sqlStr, null);
        return cInt;
    }

    public List<String> getCollectionNames() {
        final Cursor cInt = this.getCollections(0, 999, null);

        final List<String> retVal = new ArrayList<String>();
        cInt.moveToFirst();
        while (cInt.isAfterLast() == false) {
            final Object tmpObj = cInt.getString(1);
            if (!retVal.contains(tmpObj.toString())) {
                retVal.add(tmpObj.toString());
            }
            cInt.moveToNext();
        }
        cInt.close();
        Collections.sort(retVal);
        return retVal;
    }

    public Cursor getCollections(final int start, final int nbrItems, final String filterChar) {
        String sqlStr = "select * from " + Collection.DATABASE_TABLE_NAME + " UNION ALL SELECT * FROM extDb."
                + Collection.DATABASE_TABLE_NAME + " order by " + Collection.COLUMN_NAME_TITLE + " asc";

        if (filterChar != null && filterChar.equals("0")) {
            final StringBuilder likeStr = new StringBuilder(" title like");
            for (int x = 0; x <= 9; x++) {
                likeStr.append(" '" + x + "%' or title like ");
            }
            likeStr.replace(likeStr.lastIndexOf("or"), likeStr.length(), "");
            sqlStr = "select * from " + Collection.DATABASE_TABLE_NAME + " where " + likeStr
                    + " UNION ALL SELECT * FROM extDb." + Collection.DATABASE_TABLE_NAME + " where " + likeStr
                    + " order by " + Collection.COLUMN_NAME_TITLE + " asc";
        } else if (filterChar != null) {
            sqlStr = "select * from " + Collection.DATABASE_TABLE_NAME + " where title like '" + filterChar
                    + "%' UNION ALL SELECT * FROM extDb." + Collection.DATABASE_TABLE_NAME + " where title like '"
                    + filterChar + "%' order by " + Collection.COLUMN_NAME_TITLE + " asc";
        }

        final Cursor cInt = this.dbCntlr.execSql(sqlStr, null);
        return cInt;
    }

    public Cursor getCurrentlyReading(final Context cntxt) {
        return this.getCurrentlyReading(cntxt, 0, EditPreferences.getCurrentReadDuration(cntxt));
    }

    public Cursor getCurrentlyReading(final Context cntxt, final int start, final int nbrItems) {
        final String sqlStr = "select * from " + Book.DATABASE_TABLE_NAME
                + " where reading_time not null UNION ALL SELECT * FROM extDb." + Book.DATABASE_TABLE_NAME
                + " where reading_time not null order by reading_time desc limit " + start + "," + nbrItems;
        final Cursor cInt = this.dbCntlr.execSql(sqlStr, null);
        return cInt;
    }

    public Cursor getPeriodicals(final Context cntxt) {
        return this.getPeriodicals(cntxt, 0, 999);
    }

    public Cursor getPeriodicals(final Context cntxt, final int start, final int nbrItems) {
        String sqlStr = "select * from " + Periodical.DATABASE_TABLE_NAME + " UNION ALL SELECT * FROM extDb."
                + Periodical.DATABASE_TABLE_NAME + " order by " + Periodical.COLUMN_NAME_PERIODICAL_NAME + " asc";
        sqlStr = "select * from (select * from " + Periodical.DATABASE_TABLE_NAME + " UNION ALL SELECT * FROM extDb."
                + Periodical.DATABASE_TABLE_NAME + ") as p,books  where books._id=p._id  order by "
                + Periodical.COLUMN_NAME_PERIODICAL_NAME + " asc limit " + start + "," + nbrItems;
        final Cursor cInt = this.dbCntlr.execSql(sqlStr, null);
        return cInt;
    }

    public Cursor getPictures(final Context cntxt) {
        return null;
    }

    public Cursor getRecentlyAdded(final Context cntxt) {
        return this.getRecentlyAdded(cntxt, 0, EditPreferences.getRecentlyAddedDuration(cntxt));
    }

    public Cursor getRecentlyAdded(final Context cntxt, final int start, final int nbrItems) {
        final String sqlStr = "select * from " + Book.DATABASE_TABLE_NAME
                + " where added_date not null UNION ALL SELECT * FROM extDb." + Book.DATABASE_TABLE_NAME
                + " where added_date not null order by added_date desc limit " + start + "," + nbrItems;
        final Cursor cInt = this.dbCntlr.execSql(sqlStr, null);
        return cInt;
    }

    private String getSortOrderField(final BookSortBy sortBy) {
        String sortOrder;
        switch (sortBy) {
        case FILENAME:
            sortOrder = Book.COLUMN_NAME_FILE_NAME;
            break;
        case AUTHOR:
            sortOrder = Book.COLUMN_NAME_AUTHOR;
            break;
        default:
            sortOrder = Book.COLUMN_NAME_TITLE;
            break;
        }
        return sortOrder;
    }

    public void initialise() {

    }

    public void launchBook(final Context cntxt, final long id) {
        final Book bk = this.getBook(cntxt, id);
        final Intent intent = new Intent();
        intent.setAction(android.content.Intent.ACTION_VIEW);
        File file = new File("/mnt/sdcard/" + bk.file_path);
        if (!file.exists()) {
            file = new File("/mnt/extsd/" + bk.file_path);
        }
        String filetype = bk.mime_type;
        if (filetype == null) {
            filetype = "application/epub+zip";
        }
        intent.setDataAndType(Uri.fromFile(file), filetype);
        cntxt.startActivity(intent);
    }

    public void openDb() {
        this.dbCntlr.open();
    }

}
